package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.bo.Product;
import com.dao.ProductDao;
import com.db.util.DBUtil;

public class ProductDaoImpl implements ProductDao {

	@Override
	public Product insert(Product product) throws Exception {
		String insertInto = "insert into t_product(create_time, effect_time, expire_time, original_price, real_price, title, main_pic, reminder, highlights, details, actual_purchase_number, manual_purchase_number, flag, provider_id,discount,save,min_num) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(insertInto);
			ps.setTimestamp(1, new Timestamp(new Date().getTime()));
			ps.setTimestamp(2, new Timestamp(product.getEffectTime().getTime()));
			ps.setTimestamp(3,
					new Timestamp(product.getExpiredTime().getTime()));
			ps.setBigDecimal(4, product.getOriginalPrice());
			ps.setBigDecimal(5, product.getRealPrice());
			ps.setString(6, product.getTitle());
			ps.setString(7, product.getMainPic());
			ps.setString(8, product.getReminder());
			ps.setString(9, product.getHighlights());
			ps.setString(10, product.getDetails());
			ps.setInt(11, product.getActualPurchaseNumber());
			ps.setInt(12, product.getManualPurchaseNumber());
			ps.setInt(13, product.getFlag());
			if (product.getProviderId() != null) {
				ps.setLong(14, product.getProviderId());
			} else {
				ps.setNull(14, java.sql.Types.NULL);
			}
			ps.setBigDecimal(15, product.getDiscount());
			ps.setBigDecimal(16, product.getSave());
			ps.setInt(17, product.getMinNum());
			ps.execute();
			product.setProductId(new Long(DBUtil.getLastInsertId(conn)));
			
		} catch (Exception e) {
			throw e;
		} finally {
			DBUtil.closeAll(null, ps, conn);
		}
		System.out.println("product save successfully");
		return product;
	}

	@Override
	public Product update(Product product) throws Exception {

		String updateStr = "update t_product set effect_time= ?,expire_time=?,original_price=?,real_price=?,title=?,main_pic=?,reminder=?,highlights=?,details=?,actual_purchase_number=?,manual_purchase_number=?,flag=?,provider_id=?,discount=?,save=?,min_num=?,limit_num=?,total_num=? where product_id=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(updateStr);
			ps.setTimestamp(1, new Timestamp(product.getEffectTime().getTime()));
			ps.setTimestamp(2,
					new Timestamp(product.getExpiredTime().getTime()));
			ps.setBigDecimal(3, product.getOriginalPrice());
			ps.setBigDecimal(4, product.getRealPrice());
			ps.setString(5, product.getTitle());
			ps.setString(6, product.getMainPic());
			ps.setString(7, product.getReminder());
			ps.setString(8, product.getHighlights());
			ps.setString(9, product.getDetails());
			ps.setInt(10, product.getActualPurchaseNumber());
			ps.setInt(11, product.getManualPurchaseNumber());
			ps.setInt(12, product.getFlag());
			if (product.getProviderId() != null) {
				ps.setLong(13, product.getProviderId());
			} else {
				ps.setNull(13, java.sql.Types.NULL);
			}
			ps.setBigDecimal(14, product.getDiscount());
			ps.setBigDecimal(15, product.getSave());
			ps.setInt(16, product.getMinNum());
			ps.setInt(17, product.getLimitNum());
			ps.setInt(18, product.getTotalNum());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(null, ps, conn);
		}

		return product;
	}

	@Override
	public void delete(long productId) throws Exception {
		String updateStr = "delete from t_product where product_id=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(updateStr);
			ps.setLong(1, productId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(null, ps, conn);
		}

	}

	@Override
	public Product load(Product product) throws Exception {
		String updateStr = "select effect_time,expire_time,original_price,real_price,title,main_pic,reminder,highlights,details,actual_purchase_number,manual_purchase_number,flag,provider_id,discount,save,min_num,limit_num,total_num from t_product where product_id=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(updateStr);
			ps.setLong(1, product.getProductId());
			rs = ps.executeQuery();
			if (rs.next()) {
				product.setEffectTime(rs.getTimestamp("effect_time"));
				product.setExpiredTime(rs.getTimestamp("expire_time"));
				product.setOriginalPrice(rs.getBigDecimal("original_price"));
				product.setRealPrice(rs.getBigDecimal("real_price"));
				product.setTitle(rs.getString("title"));
				product.setMainPic(rs.getString("main_pic"));
				product.setReminder(rs.getString("reminder"));
				product.setHighlights(rs.getString("highlights"));
				product.setDetails(rs.getString("details"));
				product.setManualPurchaseNumber(rs.getInt("manual_purchase_number"));
				product.setFlag(rs.getInt("flag"));
				product.setProviderId(rs.getLong("provider_id"));
				product.setDiscount(rs.getBigDecimal("discount"));
				product.setSave(rs.getBigDecimal("save"));
				product.setMinNum(rs.getInt("min_num"));
				product.setLimitNum(rs.getInt("limit_num"));
				product.setTotalNum(rs.getInt("total_num"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, conn);
		}

		return null;
	}

	/**
	 * only load the basic information of product in order to avoid the memory cost
	 * @param product
	 * @return
	 * @throws Exception
	 */
	public Product loadSimplePrdt(Long productId) throws Exception {
		String updateStr = "select effect_time,expire_time,original_price,real_price,title,main_pic,reminder,highlights,actual_purchase_number,manual_purchase_number,flag,discount,save,min_num,limit_num,total_num from t_product where product_id=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(updateStr);
			ps.setLong(1, productId);
			rs = ps.executeQuery();
			if (rs.next()) {
				Product product = new Product();
				product.setEffectTime(rs.getTimestamp("effect_time"));
				product.setExpiredTime(rs.getTimestamp("expire_time"));
				product.setOriginalPrice(rs.getBigDecimal("original_price"));
				product.setRealPrice(rs.getBigDecimal("real_price"));
				product.setTitle(rs.getString("title"));
				product.setMainPic(rs.getString("main_pic"));
				product.setReminder(rs.getString("reminder"));
				product.setHighlights(rs.getString("highlights"));
				product.setManualPurchaseNumber(rs.getInt("manual_purchase_number"));
				product.setFlag(rs.getInt("flag"));
				product.setDiscount(rs.getBigDecimal("discount"));
				product.setSave(rs.getBigDecimal("save"));
				product.setMinNum(rs.getInt("min_num"));
				product.setLimitNum(rs.getInt("limit_num"));
				product.setTotalNum(rs.getInt("total_num"));
				return product;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, conn);
		}

		return null;
	}
	
	public List<Product> loadProductsOfToday() throws Exception {
		String prdtListStr = "select * from t_product where DATE_FORMAT(effect_time, '%Y %m %d') = DATE_FORMAT(current_date(), '%Y %m %d')";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<Product> prdtList = new ArrayList<Product>();
		try {
			ps = conn.prepareStatement(prdtListStr);
			rs = ps.executeQuery();
			while (rs.next()) {
				Product product = new Product();
				generateProduct(rs,product);
				prdtList.add(product);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, null);
		}
		
		if(prdtList.size()>0){
			return prdtList;
		}else{
			//if there is no product of which the effect_time is today, get the last two effective products
			prdtListStr = "select * from t_product where effect_time < current_date() and flag=1 order by effect_time desc limit 2";
			try {
				ps = conn.prepareStatement(prdtListStr);
				rs = ps.executeQuery();
				while (rs.next()) {
					Product product = new Product();
					generateProduct(rs,product);
					prdtList.add(product);
				}
				return prdtList;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw e;
			} finally {
				DBUtil.closeAll(rs, ps, conn);
			}
		}
	}

	public List<Product> loadPreviousProducts(int pageNow,int pageSize) throws Exception {
		String prdtListStr = "select * from t_product where effect_time < current_date() and flag != 0 order by effect_time desc limit "+(pageNow*pageSize-pageSize)+","+pageSize;
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Product> prdtList = new ArrayList<Product>();
		try {
			ps = conn.prepareStatement(prdtListStr);
			rs = ps.executeQuery();
			while (rs.next()) {
				Product product = new Product();
				generateProduct(rs,product);
				prdtList.add(product);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, conn);
		}
		return prdtList;
	}

	/**
	 * to generate one product
	 * @param rs
	 * @throws SQLException
	 */
	private void generateProduct(ResultSet rs, Product product) throws SQLException {
		product.setProductId(rs.getLong("product_id"));
		product.setEffectTime(rs.getTimestamp("effect_time"));
		product.setExpiredTime(rs.getTimestamp("expire_time"));
		product.setOriginalPrice(rs.getBigDecimal("original_price"));
		product.setRealPrice(rs.getBigDecimal("real_price"));
		System.out.println(product.getRealPrice());
		product.setTitle(rs.getString("title"));
		product.setMainPic(rs.getString("main_pic"));
		product.setReminder(rs.getString("reminder"));
		product.setHighlights(rs.getString("highlights"));
		product.setDetails(rs.getString("details"));
		product.setManualPurchaseNumber(rs.getInt("manual_purchase_number"));
		product.setFlag(rs.getInt("flag"));
		product.setProviderId(rs.getLong("provider_id"));
		product.setDiscount(rs.getBigDecimal("discount"));
		product.setSave(rs.getBigDecimal("save"));
		product.setMinNum(rs.getInt("min_num"));
	}

	@Override
	public int PreProductsCnt() throws Exception {
		String prdtListStr = "select count(*) from t_product where effect_time < current_date() and flag != 0";
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Product> prdtList = new ArrayList<Product>();
		try {
			ps = conn.prepareStatement(prdtListStr);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeAll(rs, ps, conn);
		}
		return 0;
	}

}
